import { Router } from "express";
import { IcallbackFn, Ioracle } from "../../types";
import dayjs from "dayjs";
import { BindParameters } from "oracledb";
import { decrypt } from "../../utils/privateKey";
import { sendMail } from "./mail";
//import { sendMail } from "./mail";
// import { IreturnObj } from "../../types"
// import { IcallbackFn } from "../../types"

export default (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  router.post("/send", (req, res) => {
    const { data } = req.body;
    if (data.length === 0) {
      res.send({
        code: 0,
        message: "沒有數據",
        data: [],
      });
    }
    //先執行oracle函數生成apply_seq_no，拿到no後插入流程並插入數據。完成申請
    //單號生成規則--MA-YYYYMMDD-XXXX MA:單號抬頭，YYYYMMDD:時間 XXXX--後綴數量
    let date: string = dayjs().format("YYYYMMDD");
    let username: string = "";
    let sql = `select xx_material_apply_seqno('${date}') as no from dual `;
    oracle
      .connChaos({ sql })
      .then((result: any) => {
        let { no } = result.data[0];
        if (no !== "error") {
          sql = "";
          let bind: Array<BindParameters> = [];
          let org_code: string;
          let seq = 1;//記錄單子條數
          JSON.parse(data).forEach((item: any) => {
            sql = `insert into xx_material_replace_apply values (:no,:material_originA,:material_replaceA,:material_product,:replace_reason,:comments,:unit,:amount,to_date(:start_time,'YYYY-MM-DD'),'',:applier,sysdate,20,'M10審核-1','N',:applier,sysdate,:org_code,:apply_seq,'Y')`;
            let temp1 = { no: no,apply_seq:seq };
            username = item.applier = decrypt(item.applier);
            item = { ...temp1, ...item };
            bind.push(item);
            org_code = item.org_code;
            seq ++;
          });
          oracle
            .connChaos({ sql, isMulti: true, params: bind })
            .then((result) => {
              if (result) {
                //申請人送出時直接跳到二階流程
                let sql = `insert into xx_material_replace_apply_flow values(:no,:seq,:phase,sysdate,:klm,:status,'',sysdate)`;
                let bindApplier: Array<BindParameters> = [
                  {
                    no: no,
                    seq: 10,
                    phase: "申請開單",
                    klm: username,
                    status: "Y",
                  },
                ];
                oracle
                  .connChaos({ sql, isMulti: true, params: bindApplier })
                  .then((result) => {
                    if (result) {
                      sql = ` insert into xx_material_replace_apply_flow values(:no,:seq,:phase,sysdate,(select boss_account from department where code = :boss),:status,'','')`;
                      let bind: Array<BindParameters> = [
                        {
                          no: no,
                          seq: 20,
                          phase: "M10審核-1",
                          boss: org_code + "M10",
                          status: "",
                        },
                        {
                          no: no,
                          seq: 30,
                          phase: "M00審核-1",
                          boss: org_code + "M00",
                          status: "",
                        },
                        {
                          no: no,
                          seq: 40,
                          phase: "L20審核-1",
                          boss: org_code + "L20",
                          status: "",
                        },
                        {
                          no: no,
                          seq: 50,
                          phase: "L00審核-1",
                          boss: org_code + "L00",
                          status: "",
                        },        
                        {
                          no: no,
                          seq: 60,
                          phase: "L20處理-2",
                          boss: org_code + "L20",
                          status: "",
                        },      
                        {
                          no: no,
                          seq: 70,
                          phase: "L00審核-2",
                          boss: org_code + "L00",
                          status: "",
                        },         
                      ];
                      oracle
                        .connChaos({ sql, isMulti: true, params: bind })
                        .then((result) => {
                          if (result) {
                            // 發送郵件
                            sql =
                              " select b.chinese_name,substr(a.klm,9)||'@compeq.huizhou.gd.cn' email from xx_material_replace_apply_flow a,user_info_n b";
                            sql += ` where a.flow_code = 20 and a.klm = b.account and a.docid = '${no}'`;
                            oracle
                              .connChaos({ sql })
                              .then((result) => {
                                if (result.data.length > 0) {
                                  const { chinese_name, email } = result.data[0];
                                  sendMail({
                                    mailTo: email as string,
                                    mailSubject: "物料替代系統-簽核通知郵件",
                                    mailHtml:
                                      "./mail/material_substitution/send_approve.ejs",
                                    mailData: { chinese_name, email },
                                  }).then(()=>{
                                    res.send({code:200,message:'申請成功',data:[]})
                                  }).catch((err)=>{
                                    res.send({code:0,message:err})
                                  });
                                }
                              })
                              .catch(() => {
                                res.send({ code: 0, message: "數據庫異常！" });
                              });
                          }
                        })
                        .catch(() => {
                          res.send({ code: 0, message: "數據庫異常！" });
                        });
                    }
                  })
                  .catch(() => {
                    res.send({ code: 0, message: "數據庫異常！" });
                  });
              }
            })
            .catch(() => {
              res.send({ code: 0, message: "數據庫異常！" });
            });
        } else {
          res.send({ code: 0, message: "申請單據過超負荷" });
        }
      })
      .catch(() => {
        res.send({ code: 0, message: "數據庫異常" });
      });
    //
  });

  router.post("/get", (req, res) => {
    const { docid } = req.body;
    let sql = ` select docid,current_flow,current_flow_phase,material_origina,material_replacea,material_product,replace_reason,replace_comments,unit,amount,start_time,apply_seq`;
    sql += ` from xx_material_replace_apply where docid = '${docid}' and current_flow = 10`;
    callbackFn(res, oracle.connChaos, { sql });
  });

  router.post("/segmentcheck", (req, res) => {
    const { segment } = req.body;
    let sql = ` select count(*)count_flag,description from xxcm_mtl_system_item_xtmp where segment1 = '${segment}' group by segment1,description`;
    callbackFn(res, oracle.connChaos, { sql });
  });

  router.post("/segmentcheckPatch", (req, res) => {
    let { segment } = req.body;
    segment = JSON.parse(segment);
    let sql = " select ";
    for (let i = 0; i < segment.length; i++) {
      if (i == 0) {
        sql += `(select count(*) from xxcm_mtl_system_item_xtmp where segment1 = '${
          segment[i]
        }')count_${i + 1}`;
      } else {
        sql += `,(select count(*) from xxcm_mtl_system_item_xtmp where segment1 = '${
          segment[i]
        }')count_${i + 1}`;
      }
    }
    sql += " from dual";
    callbackFn(res, oracle.connChaos, { sql });
  });
  return router;
};
